<?php
namespace Modules\Admin\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class DatabaseController extends CommonController
{
    /**
     * @name  列表
     */
    public function index()
    {
        return view('admin::data_base.index');
    }
    /**
     * @name  列表数据
     */
    public function data()
    { 	
		$tables = DB::select("SHOW TABLE STATUS LIKE '" . config('database.connections.mysql')['prefix'] ."%'");
		$total = 0;
		$arr = [];
		//便利表
		foreach ($tables as $k => $v){
			//调用PHP格式化字节大小函数
			$arr[$k]['Name'] = $v->Name;//数据表名
			$arr[$k]['Rows'] = $v->Rows;//数据条数
			$arr[$k]['Engine'] = $v->Engine;//类型
			$arr[$k]['Collation'] = $v->Collation;//排序规则
			$arr[$k]['Create_time'] = $v->Create_time;//创建时间
			$arr[$k]['Comment'] = $v->Comment;//备注
			$arr[$k]['size'] = format_bytes($v->Data_length);//占用空间
			$total += $v->Data_length;
		}
        return response()->json([
			'code' => 0,
			'msg'=>config('admin.limit_msg'),
			'data'=>$arr,
			'rel'=>1,
			'tableNum'=>count($arr),
			'total'=>format_bytes($total)
		]);
    }
	/**
     * @name  详情页
     */
	public function table_detail(Request $request)
    {
		$table = $request->get('table');
		$comment = $request->get('comment');
        return view('admin::data_base.table_detail',compact('table','comment'));
    } 
	/**
     * @name  表数据
     */
    public function table_data(Request $request)
    { 	
		$table = DB::select("select * from information_schema.COLUMNS where table_name='".$request->get('table')."'");
		$total = 0;
		$arr = [];
		//便利表
		foreach ($table as $k => $v){
			//调用PHP格式化字节大小函数
			$arr[$k]['COLUMN_NAME'] = $v->COLUMN_NAME;//字段名
			$arr[$k]['COLUMN_TYPE'] = $v->COLUMN_TYPE;//数据类型
			$arr[$k]['COLUMN_DEFAULT'] = $v->COLUMN_DEFAULT;//默认值
			$arr[$k]['IS_NULLABLE'] = $v->IS_NULLABLE;//允许非空
			$arr[$k]['EXTRA'] = ($v->EXTRA == 'auto_increment'?'是':' ');//自动递增
			$arr[$k]['COLUMN_COMMENT'] = $v->COLUMN_COMMENT;//备注
		}
        return response()->json([
			'code' => 0,
			'msg'=>config('admin.limit_msg'),
			'data'=>$arr,
			'rel'=>1,
			'tableNum'=>count($arr),
		]);
    } 
	/**
     * @name  备份
     */
    public function backup(Request $request){
        $tables = $request->get('tables');
        if(empty($tables)){
			$dataList = DB::select("SHOW TABLE STATUS LIKE '" . config('database.connections.mysql')['prefix'] ."%'");
            foreach ($dataList as $row){
                $tables[]= $row->Name;
            }
        }
		//输出文件头部信息
        $sql = "-- SONGBO SQL Backup\n-- Time:".date('Y-m-d H:i:s')."\n-- " . getHttpType() . " \n\n";
        foreach($tables as $key=>$table){
			//表信息
            $sql .= "--\n-- 表的结构 `$table`\n-- \n";
            $sql .= "DROP TABLE IF EXISTS `$table`;\n";
			//打开表
            $info = DB::select("SHOW CREATE TABLE  $table");
            //查出表结构
			$sql .= str_replace(array('USING BTREE','ROW_FORMAT=DYNAMIC'),'',json_decode(json_encode($info), true)[0]['Create Table']).";\n";
            //打开表
			$result = DB::select("SELECT * FROM $table");
			$result = json_decode(json_encode($result), true);
            //判断表中是否有数据
			if($result){
				$sql .= "\n-- \n-- 导出`$table`表中的数据 `$table`\n--\n";
				//便利数据
				foreach($result as $key=>$val) {
					foreach ($val as $k=>$field){
						if(is_string($field)) {
							$val[$k] = '\''. $field.'\'';
						}elseif($field==0){
							$val[$k] = 0;
						} elseif(empty($field)){
							$val[$k] = 'NULL';
						}
					}
					//将数据存入$sql中
					$sql .= "INSERT INTO `$table` VALUES (".implode(',', $val).");\n";
				}
			}
        }
        //打开文件存入数据
		$r= file_put_contents(public_path('data') . '/' . date('YmdHis') . rand(1000000,9999999) . '.sql', trim($sql));
		return response()->json([
				'status'=>1,
				'error' => '',
				'msg' => '备份数据库成功！'
		],200);
		
    }
	/**
     * @name  备份文件列表
     */
    public function restore(){
		return view('admin::data_base.restore');
	}
	/**
     * @name  备份文件列表
     */
    public function restore_data(){
			//定义查询文件后缀
            $pattern = "*.sql";
			//glob() 函数返回匹配指定模式的文件名或目录。
			//该函数返回一个包含有匹配文件 / 目录的数组。如果出错返回 false。
            $filelist = glob(public_path('data') . '/' .$pattern);
            $fileArray = array();
            foreach ($filelist  as $i => $file) {
                //只读取文件
                if (is_file($file)){
					//取得文件大小
                    $_size = filesize($file);
					//函数返回路径中的文件名部分
                    $name = basename($file);
                    $fileArray[] = array(
                        'name' => $name,
                        'time' => date('Y-m-d h:i',filemtime($file)),
                        'sortSize' => format_bytes($_size),
                        'size' => $_size,
                    );
                }
            }
			//判断文件是否存在
            if(empty($fileArray)) $fileArray = array();
            return ['code'=>0,'msg'=>config('common.acquire_success'),'data'=>$fileArray,'rel'=>1];
    }
	/**
     * @name  下载sql文件
     */
    public function down_file(Request $request) {
		$filePath = public_path('data') . '/'  . $request->get('name');
        if (!file_exists($filePath)) {
            return s_error('下载文件不存在！',route('admin.data_base.restore'));
        }
		//下载文件
        $filename = basename($filePath);
        header("Content-type: application/octet-stream");
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header("Content-Length: " . filesize($filePath));
        readfile($filePath);
    }
	/**
     * @name  删除sql文件
     */
    public function del_sql_files(Request $request) {
        if(unlink(public_path('data') . '/' . $request->get('name'))){
			return response()->json([
				'status'=>1,
				'error' => '',
				'msg' => config('admin.delete_success')
			],200);
        }else{
			return response()->json([
				'status'=>0,
				'error' => config('admin.delete_error'),
				'msg' => ''
			],201);
		}
        
    }
}